package repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GenericConector{
	
	private static String databaseName = "grupo_1_db";
	private static String url = "jdbc:mysql://localhost:3306/"+databaseName;    
    private static String driverName = "com.mysql.jdbc.Driver";   
    private static String username = "root";   
    private static String password = "ignacio";
    private static Connection con;
    private static Statement sta;
      
    private static void startConnection() {
        try {
            Class.forName(driverName);
            try {
                con = DriverManager.getConnection(url, username, password);
            } catch (SQLException ex) {
                System.out.println("Fallo la conexion con la base de datos"); 
            }
        } catch (ClassNotFoundException ex) {
            System.out.println("Driver not found."); 
        }
        
    }
    
    private static void startStatement() throws SQLException{
    	sta = con.createStatement();    	
    }
    
    public void ejecutar(String consulta) throws SQLException{
     	startConnection();
    	startStatement();
    	sta.executeUpdate(consulta);  	
    }
    
    
    public ResultSet getResultSet (String consulta) throws SQLException
    {
    	startConnection();
    	startStatement();
    	return sta.executeQuery(consulta);
    }
    
    
    
    public ResultSet getUltimaReserva() throws SQLException
    {
    	return getResultSet("SELECT ifnull(max(idreservas),0) as max FROM reservas;");
    }
    
    public ResultSet getVuelos() throws SQLException {
    	return getResultSet("select idvuelo,descripcion from vuelos");    
	}
    
    public ResultSet getPaquetes() throws SQLException {
    	return getResultSet("select * from paquetes_viajes");    
	}
    
    public ResultSet getClientes() throws SQLException {
    	return getResultSet("select id,usuario from clientes");    
	}
    
    public ResultSet getDatosReserva(String idReserva) throws SQLException {
    	return getResultSet("SELECT idpaquete,idvuelo from reservas where idreservas = " + idReserva);    
	}   
    
    
    public ResultSet getListaPaquetesHechos() throws SQLException {
    	return getResultSet("SELECT  idreservas, concat(\"Nro Reserva: \" ,idreservas,\" -- Paquete: \", p.descripcion, \" -- Vuelo: \" , v.descripcion ) as Paquete FROM grupo_1_db.reservas r inner join paquetes_viajes p on p.id=r.idpaquete inner join vuelos v on r.idvuelo = v.idvuelo");    
	}
    
}
